const connection = require('../app/database');

class MomentService {
    // 创建动态
    async created(content, userId) {
        try {
            // 拼接 SQL 语句
            const statement = `INSERT INTO moment (content, user_id) VALUES (?,?);`;

            // 执行 SQL 语句
            const [result] = await connection.execute(statement, [content, userId]);

            return result;
        } catch (error) {
            console.error('MomentService - created:', error);
            throw error; // 抛出异常，让调用者处理
        }
    }

    // 查询动态列表
    async queryList(offset, size) {
        try {
            // 拼接 SQL 语句
            const statement = `
                SELECT 
                    m.id id, m.content content, m.create_At create_At, m.update_At update_At,
                    JSON_OBJECT('id', u.id, 'name', u.name, 'create_At', u.creata_At,'update_At',u.update_At,'avatarURL',u.avatar_url) user,
                    (SELECT COUNT(*) FROM comment WHERE comment.moment_id = m.id) commentCount,
                    (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount
                FROM moment m
                LEFT JOIN user u ON u.id = m.user_id
                LIMIT ? OFFSET ?;`;

            // 执行 SQL 语句
            const [result] = await connection.execute(statement, [size, offset]);

            return result;
        } catch (error) {
            console.error('MomentService - queryList:', error);
            throw error; // 抛出异常，让调用者处理
        }
    }

    // 查询动态详情
    async queryParams(momentId) {
        try {
            // 拼接 SQL 语句
            const statement = `
                SELECT 
                    m.id id, m.content content, m.create_At create_At, m.update_At update_At,
                    JSON_OBJECT('id', u.id, 'name', u.name,'avatarURL',u.avatar_url, 'create_At', u.creata_At,'update_At',u.update_At) user,
                    (
                        SELECT 
                            JSON_ARRAYAGG(JSON_OBJECT(
                                'id',c.id,'content',c.content,'comment_id',comment_id,'user',
                                JSON_OBJECT(
                                    'id',cu.id, 'name',cu.name,'avatarURL',cu.avatar_url)
                            )) commments
                        FROM comment c
                        LEFT JOIN user cu ON c.user_id = cu.id
                        WHERE c.comment_id = m.id
                    ) comments,
                    (
                        SELECT 
                            JSON_ARRAYAGG(JSON_OBJECT(
                                'id',l.id,'name',l.name
                            )) 
                    ) labels
                FROM moment m
                LEFT JOIN user u ON u.id = m.user_id
                LEFT JOIN moment_label ml ON ml.moment_id = m.id
                LEFT JOIN label l ON ml.label_id = l.id
                WHERE m.id = ?
                GROUP BY m.id;
            `;

            // 执行 SQL 语句
            const [result] = await connection.execute(statement, [momentId]);

            return result;
        } catch (error) {
            console.error('MomentService - queryParams:', error);
            throw error; // 抛出异常，让调用者处理
        }
    }

    // 更新动态
    async updated(momentId, content) {
        try {
            // 拼接 SQL 语句
            const statement = `UPDATE moment SET content = ? WHERE id = ?`;

            // 执行 SQL 语句
            const [result] = await connection.execute(statement, [content, momentId]);

            return result;
        } catch (error) {
            console.error('MomentService - updated:', error);
            throw error; // 抛出异常，让调用者处理
        }
    }

    // 删除动态
    async remove(id) {
        try {
            // 拼接 SQL 语句
            const statement = 'DELETE FROM moment WHERE id = ?;';

            // 执行 SQL 语句
            const [result] = await connection.execute(statement, [id]);

            return result;
        } catch (error) {
            console.error('MomentService - remove:', error);
            throw error; // 抛出异常，让调用者处理
        }
    }

    // 检查动态是否有指定标签
    async hasLabel(momentId, labelId) {
        try {
            // 拼接 SQL 语句
            const statement = 'SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?;';

            // 执行 SQL 语句
            const [result] = await connection.execute(statement, [momentId, labelId]);

            return !!result.length; // 返回是否有结果的布尔值
        } catch (error) {
            console.error('MomentService - hasLabel:', error);
            throw error; // 抛出异常，让调用者处理
        }
    }

    // 为动态添加标签
    async addLabel(momentId, labelId) {
        try {
            // 拼接 SQL 语句
            const statement = 'INSERT INTO `moment_label` (moment_id, label_id) VALUES (?,?);';

            // 执行 SQL 语句
            const [result] = await connection.execute(statement, [momentId, labelId]);

            return result;
        } catch (error) {
            console.error('MomentService - addLabel:', error);
            throw error; // 抛出异常，让调用者处理
        }
    }
}

module.exports = new MomentService();
